﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OverStore.Configuration;
using OverStore.Exceptions;
using OverStore.Configuration.Components;
using OverStore.Runtime.Storage;
using OverStore.Configuration.Builder;
using OverStore.CompositeIdentifiers;
using OverStore.Sql;
using OverStore.Sql.TableSchema;
using System.Reflection;
using OverStore.Configuration.Builder.Fluent;
using OverStore.Runtime.ObjectRepository.Mapping;
using OverStore.Configuration.ObjectRepository.CommandSet;

namespace OverStore.TutorialAsTest.Tutorial1.Lesson1
{

    /// <summary>
    /// Here we start learn how to operate with objects using its identifiers.
    /// 
    /// In this lessons we consider only very simple objects, which properties contains 
    /// only scalar values (e.w. no references to other objects and collections).
    /// 
    /// For test we will use sample database named MyMoney.sdf.
    /// </summary>
    [TestClass]
    public class Lesson1
    {
        /* *********************
         *          IDENTIFIERS
         * 
         * The one of the main consept in any ORM is object identification.
         * Every object has identifier. It is not just regarding ORM, it 
         * is wide consept covers any types of storages, including databases and 
         * memory. 
         * Quick explaination what is object identifier. Object identifier is 
         * small result allows to access object data in special manner. 
         * For object in memory the object identifier is memory address points to 
         * object data. In database (where naturally no objects, but tables and rows etc.) 
         * object analog is single row of specified table, and object identifier is 
         * the row primary key. 
         * 
         * ORM main functionallity is convert row in specified DB table into 
         * object of specified type in memory and perform back conversion.
         * 
         * (Of course, such operation is not so simple and requires a lot of additional steps,
         * and they will be considered in further chapters).
         * 
         * But sinse we're considering object identifiers, lets continue.
         * So, there are two types of identifier:
         *      Database: Table.Row.PrimaryKey
         *      Memory  : Object address
         * Because CLR and C# does not support address operation in verificable safe code, 
         * object address is object itself, accessible via variable.
         * 
         * So, OverStrore needs to map these two types of identifiers.
         * This process names ReadIdentifier Mapping (more popular spelling is Identity Mapping),
         * and structure supports such process named ReadIdentifier Map (Identity Map).
         * 
         * There are only two things are required for identity mapping: database identifier 
         * and object address (object instance). It has few very important conclusion:
         *  1) It is not required for object to be loaded with data for take part in identity mapping
         *  2) It is not required for object to contains its identifier as part of its data.
         *  3) Object may know nothing about its identifier, and event about thing like identifier exists in the world.
         *  4) It is not required for object to override Equals and GetHashCode methods, because 
         *      object equality type required for correct identity mapping is reference equality, that
         *      supported by static object method ReferenceEquals.
         *  5) Database identifier assigned to object is immutable because it stored internally in
         *      session  and is not accessible outside session.
         *      
         *      New identifier generation.
         * In case of loading object from database, we know database identifier
         * and session creates new instance (if instance with such identifier does not exist yet)
         * and assign database identifier to it. But session should addition of new objects to 
         * session, and session should assign a database identifier to new instance.
         * There are few types of new identifier generation algorithm, but the main 
         * two types are session-generated identifiers and object-generated identifier.
         * They differ by object responsible for providing new database identifier.
         * 
         *      ReadIdentifier syncronization.
         * As we consider above, new instance added to session gets new database identifier.
         * But often database has own algorithm for generation new primary keys, like IDENTITY columns
         * or SEQUENCES, and database identifiers generated by databases do not match identifiers
         * assigned to new objects stored in session. It is normal situation, because we usually can't get
         * actual database identifier till moment of writing object's data into database. But after 
         * writing and getting actual database identifier for object session should update identifier
         * to its actual result. This process names identifier syncronisation, and essential of such 
         * process is removing pair <db identifier - object> from Identity Map and add new pair 
         * <new db identifier - object> with new identifier and same object to Identity Map.
         * 
         * ReadIdentifier syncornisation often requires additional steps: 
         * 1) If object holds identifier copy as part of object data, session needs to update object 
         * data for reflect identifier changing. Fortunately, it usually automatically performs by Mapper.
         * 2) If object has mutable identifiers (identifier result contains in object data and 
         *      can be modifier outside of session), the working with identifier is more complex. 
         *      Avoid mutable identifiers if possible because they require more attention and 
         *      they are more complex in maintanance.
         * 
         * Here we consider how to work with mutable identifiers, no matter they are composite identifiers or 
         * scalar identifier, and why they are so complex.
         * 
         * Mutable identifiers causes few moments that require to pay attention to:
         * 1) New identifier assignment. It may be a problem for composite identifiers. 
         *    Algorithm generates new composite identifiers must be implemented in way to avoid matches 
         *    identifiers of existing objects. This problem may be solved in few ways, 
         *    for example by true generation of unique combination, or by using the fact that type of object 
         *    represents new database identifier in memory is not required to match type of object represents existing database identifier. 
         *  
         * 2) Inserting of new objects to database. Session should not use data of database identifier 
         *    for inserting, instead it should get values from object data. Fortunately, it is default
         *    behavior of inserting process. After successful inserting, database identifier of object 
         *    should be syncronized using actual data inserted in row, no matter is identifier composite 
         *    or scalar, and is identifier result generated by database or not.
         *      
         * 3) Deleting objects from database. Due deleting session should use database identifiers instead of object data because 
         *    object data are mutable and object's copy of identifier may be changed. It causes deleting of another object or 
         *    error if there is no row with such identifier in database.
         * 
         * 4) Updating objects in database. Updating objects with mutable identifier is complex action.
         *    SQL UPDATE statement consists of few parts, but now we are interesting with WHERE part, 
         *    which define actual row for updating, and SET part, which deinfes new values for row.
         *    S, WHERE part should use database identifier, but not object data, and reason for this is same as for 
         *    deleting object. But SET part should use actual object data for correctly update identifier, if it is 
         *    changed. After successful updating, database identifier of object should be syncronized using actual row data.
         *    
         * 
         * 
         * *********************/

        #region The Simplest GetByIdentifier

        //*****************
        // This is simplest test example shows how to get single object 
        // from database using OverStore.
        // 
        // We will use table ItemType from our test database.
        // This is very simple table contains three columns.
        //
        // ItemType defines types of item. Now we have two types of item: 
        // 'Product or Service', and 'Currency'.
        //  
        // Full Content of ItemType table:
        //
        // ID    Name                   Description
        // ------------------------------------------
        // 1	Product or Service	    NULL
        // 2    Currency	            NULL
        //*****************

        class ItemType
        {
            public int Type
            {
                get;
                set;
            }

            public string Name
            {
                get;
                set;
            }

            public string Description
            {
                get;
                set;
            }
        }

        static IComponentConfiguration<IMapper<ItemType>> CreateItemTypeMapper()
        {
            return Component.Mapper.Delegate<ItemType>(
                (item, name, p) =>
                {
                    if (name == "Name")
                        return item.Name;
                    if (name == "Description")
                        return item.Description;
                    if (name == "Type")
                        return item.Type;
                    if (name == "orig__Type")
                        return p.GetIdentifier<ItemType, int>(item);
                    return Missing.Value;
                },
                (data, item, p) =>
                {
                    item.Name = data.GetString("Name");
                    item.Description = data.GetString("Description");
                    if (data.ContainsName("orig__oid"))
                        item.Type = data.GetInt32("orig__oid");
                });
        }

        private string CreateMyMoneyDbCopyAndConnectionString()
        {
            return TestHelper.CreateMyMoneyCopyAndBuildConnectionString();
        }

        private static OverStoreConfiguration CreateConfigItemType(string connectionString)
        {
            return OverStoreFluent.Configure()
                    .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                    .CustomClassRepository<ItemType, int>()
                        .WithDepot(Component.Depot.WithInternalKey<ItemType, int>("Type"))
                        .WithMapper(CreateItemTypeMapper())
                        .CommandsFromTable(false, Component.CreateTable("ItemType")
                                       .Key<int>("Type")
                                       .Column<string>("Name")
                                       .Column<string>("Description"))
                       .ByIdentifierAttaching(i => i.Type)
                       .ParameterlessReader(true)
                            .WithCommand(Component.AdoCommand.ReadCommand("select * from ItemType"))
                            .DelegateIdentifierProvider(r => r.GetInt32("ItemType"))
                        .EndReader()
                        .ParameterizedReader<ItemType>(true)
                            .ReadCommand()
                                .UseSqlCommand("select * from ItemType")
                            .EndCommand()
                            .MapParametersUsingReflection()
                            .DelegateIdentifierProvider(r => r.GetInt32("ItemType"))
                        .EndReader()
                     .EndObjectRepository()
                .End();
        }

        private static OverStoreConfiguration CreateConfigItemTypeWithObjectIdentifier(string connectionString)
        {
            return OverStoreFluent.Configure()
                    .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                    .CustomClassRepository<ItemType, object>()
                        .WithDepot(Component.Depot.WithInternalKey<ItemType, object>(
                                            i => i.Type, () => new ItemType()))
                    .WithMapper(CreateItemTypeMapper())
                    .CommandsFromTable(false, 
                        Component.CreateTable("ItemType")
                                 .Key<int>("Type")
                                 .Column<string>("Name")
                                 .Column<string>("Description"))
                    .ByIdentifierAttaching(i => i.Type)
                .EndObjectRepository()
            .End();
        }

        /// <summary>
        /// Simple example how to access object via identifier.
        /// </summary>
        [TestMethod]
        public void GetObjectSimpleGet()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            var config = CreateConfigItemType(connectionString);
            using (var session = config.CreateSession())
            {
                // We should specify correct type of item and type of item identifier.
                var item = session.GetByIdentifier<ItemType, int>(1); // Should get ItemType class for Product or Service.
                Assert.AreEqual(1, item.Type); // Check OverrideDbType
                Assert.AreEqual("Product or Service", item.Name); // Check Name
            }
        }

        [TestMethod]
        public void GetObjectSimpleGetOrDefault()
        {
            var cs = CreateMyMoneyDbCopyAndConnectionString();
            var config = CreateConfigItemType(cs);
            using (var session = config.CreateSession())
            {
                var item = session.GetByIdentifierOrDefault<ItemType, int>(100); // Must returns null because there is no item type with id 100;
                Assert.IsNull(item);
                item = session.GetByIdentifierOrDefault<ItemType, int>(100);
                Assert.IsNull(item);

                item = session.GetByIdentifierOrDefault<ItemType, int>(1);
                Assert.AreEqual(1, item.Type); // Check OverrideDbType
                Assert.AreEqual("Product or Service", item.Name); // Check Name
            }
        }

        /// <summary>
        /// Trying to access object using identifier which does not exists in database.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(PersistenceOperationFailedException))]
        public void GetObjectUnknownIdentifier()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            var config = CreateConfigItemType(connectionString);
            var session = config.CreateSession();
            session.GetByIdentifier<ItemType, int>(10);
            // Should get PersistenOperationFailedException.
        }

        /// <summary>
        /// The type of object identifier should be specified correctly.
        /// The correct type of identifier defines by configuration. 
        /// We can use System.Object as universal type of identifier, but 
        /// because there is no support for generics covariance in C# we can't 
        /// configure repository with identifier of type integer but request 
        /// object using object as identifier type.
        /// 
        /// Trying to access object using wrong identifier type.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(IdentifierTypeIsInvalidException))]
        public void GetObjectWrongIdentifierType()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            var config = CreateConfigItemType(connectionString);
            var session = config.CreateSession();
            // We have item with OverrideDbType = 1, but identifier type is Int32 not String.
            var item = session.GetByIdentifier<ItemType, string>("1");
            // Should get TypePersistenceNotSupportedException.
        }

        /// <summary>
        /// We can get objects of known types only. Each object type 
        /// allowed to get has corresponding configuration.
        /// 
        /// When we try to get object of type which not specified 
        /// in configuration, we will get exception throwing.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(TypePersistenceNotSupportedException))]
        public void GetObjectWrongType()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateConfigItemType(connectionString).CreateSession())
            {
                session.GetByIdentifier<string, int>(1);// No storage for string exists.
                // Should get exception.
            }
        }

        /// <summary>
        /// Persistence session maintains internal map named Identity Map.
        /// It allows load object of specified type assosiated with specified identifier
        /// only once. It prevents many bad things: changes inconsistense, circular reference 
        /// errors, and many other kinds of circularing.
        /// </summary>
        [TestMethod]
        public void GetObjectIdentityMapping()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            var config = CreateConfigItemType(connectionString);
            using (var session = config.CreateSession())
            {

                var item1 = session.GetByIdentifier<ItemType, int>(1);
                var item2 = session.GetByIdentifier<ItemType, int>(2);
                // Item1 and Item2 should be different instances.
                Assert.AreNotSame(item1, item2);

                // Gets object with identifiers 1 and 2 again.
                var item1Trying2 = session.GetByIdentifier<ItemType, int>(1);
                var item2Trying2 = session.GetByIdentifier<ItemType, int>(2);
                // They should still different instances.
                Assert.AreNotSame(item1Trying2, item2Trying2);
                // But query of item with same identifier should return same instance.
                Assert.AreSame(item1, item1Trying2);
                Assert.AreSame(item2, item2Trying2);
                // For be sure repeat getting few times.
                Assert.AreSame(item1, session.GetByIdentifier<ItemType, int>(1));
                Assert.AreSame(item1, session.GetByIdentifier<ItemType, int>(1));
                Assert.AreSame(item1, session.GetByIdentifier<ItemType, int>(1));
                Assert.AreSame(item1, session.GetByIdentifier<ItemType, int>(1));
                Assert.AreSame(item1, session.GetByIdentifier<ItemType, int>(1));
                Assert.AreSame(item1, session.GetByIdentifier<ItemType, int>(1));
            }
        }

        /// <summary>
        /// Null is not valid identifier for object. So if we 
        /// try to get object with null identifier, we will get 
        /// ArgumentNullException.
        /// </summary>
        [TestMethod]
        [ExpectedException(typeof(ArgumentNullException))]
        public void GetObjectNullIdentifier()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateConfigItemTypeWithObjectIdentifier(connectionString).CreateSession())
            {
                // If we try to get object with null identifier, we should get ArgumentNullException.
                session.GetByIdentifier<ItemType, object>(null);
            }
        }

        [TestMethod]
        [ExpectedException(typeof(ObjectDisposedException))]
        public void GetObjectDisposedSession()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateConfigItemType(connectionString).CreateSession())
            {
                session.Dispose();
                session.GetByIdentifier<ItemType, int>(1);
            }
        }

        private static OverStoreConfiguration CreateConfigItemTypeInvalidTableName(string connectionString)
        {
            return OverStoreFluent.Configure()
               .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
               .CustomClassRepository<ItemType, int>()
                   .WithDepot(Component.Depot.WithInternalKey<ItemType, int>(i => i.Type, () => new ItemType()))
                   .WithMapper(CreateItemTypeMapper())
                   .CommandsFromTable(false, Component.CreateTable("TableNotExists")
                                       .Key<int>("Type")
                                       .Column<string>("Name")
                                       .Column<string>("Description"))
                   .ByIdentifierAttaching(i => i.Type)
               .EndObjectRepository()
           .End();
        }

        [TestMethod]
        [ExpectedException(typeof(PersistenceOperationFailedException))]
        public void GetObjectError()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateConfigItemTypeInvalidTableName(connectionString).CreateSession())
            {
                session.GetByIdentifier<ItemType, int>(1);
            }
        }

        /// <summary>
        /// Object keys are immutable. This means event if object contains 
        /// result of its key, changing result does not affect result of key 
        /// used for access this instance from session.
        /// 
        /// Object keys may be changed during saving object, see further lessons 
        /// for details.
        /// </summary>
        [TestMethod]
        public void GetObjectKeyImmutablility()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateConfigItemType(connectionString).CreateSession())
            {
                var item1 = session.GetByIdentifier<ItemType, int>(1); // Get object by key result.
                Assert.AreEqual(1, item1.Type);

                try
                {
                    // Check object with type == 100 does not exists.
                    session.GetByIdentifier<ItemType, int>(100);
                    Assert.Fail("ItemType with type = 100 is exists in database");
                }
                catch (PersistenceOperationFailedException)
                {
                    // Omit exception because it means that object with type == 100 does
                    // not exists, and it is correct.
                }

                item1.Type = 100; // Change actual result of key.

                // Object should still available by old key.
                var item2 = session.GetByIdentifier<ItemType, int>(1);
                Assert.AreSame(item1, item2);

                // No objects should be available by key == 100
                try
                {
                    session.GetByIdentifier<ItemType, int>(100);
                    Assert.Fail("Object should not be available after change key property");
                }
                catch (PersistenceOperationFailedException)
                {
                }
            }
        }

        #endregion

        #region Get object using synonym type

        /* ********************************************
         * Here we consider how to use synonym types.
         * There are few types of synonym: redirection, conversion, inheritance. 
         * 
         * Redirection allows usage of one type as type argument for 
         * persistence session methods, but processing requests as for another assignable type. 
         * OverrideDbType used for calling methods of persistence session should be assignable
         * from type actually performs processing. Redirection are useful for access implementation
         * using inteface.
         * 
         * We will use UserAccount table.
         * Content of UserAccount table
         * ID                                   Login   Password                            Name            Description     CreatedBy                               CreatedDate         EditedBy                                EditedDate          DeletedBy   DeletedDate
         * ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         * c0a23bf2-8266-440c-95e5-7ce2830687e2	system	F38C96D1ED81436CAFE2E962DA6ABBDF	System Account	NULL	        c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00	c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00	NULL	    NULL	
         * 6a148282-8a18-4b72-b577-cf2f1005df65	admin	admin	                            Administrator	NULL	        c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00	c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00	NULL	    NULL	
         * af7989bb-1acb-4d00-a49a-03551c3857cc	sergey	pladder	                            Sergey Tokarev	NULL	        c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00	c0a23bf2-8266-440c-95e5-7ce2830687e2	22.05.2009 0:00:00	NULL	    NULL	
         * 
         * Some description. Here is simple table contains data for User accounts registered in system. 
         * Fields Id, Login, Password, Name, Description not requires explaination, I hope.
         * Rest of fields (CreatedBy, CreatedDate, ...) contains audit information about row changes.
         * Fields CreatedBy, EditedBy, DeletedBy contains identifiers of user accounts who performs corresponding operation.
         * It references to table UserAccount, so it references to itself.
         * 
         */

        /// <summary>
        /// Define simple interface contains information relevant to 
        /// information in IUserAccount table.
        /// </summary>
        interface IUserAccount
        {
            Guid Id
            {
                get;
            }

            string Login
            {
                get;
            }
        }

        /// <summary>
        /// Note that we define class contains only part of information from table.
        /// It always works fine for read, but we probably has problem with save, because 
        /// we may miss values for fields without default values provided.
        /// </summary>
        class UserAccountPartial : IUserAccount
        {
            public Guid Id
            {
                get;
                set;
            }

            public string Login
            {
                get;
                set;
            }

            public string Name
            {
                get;
                set;
            }
        }

        static IComponentConfiguration<IMapper<UserAccountPartial>> CreateUserAccountPartialMapper()
        {
            return Component.Mapper.Delegate<UserAccountPartial>(
                (item, name, p) =>
                {
                    if (name == "Id")
                        return item.Id;
                    if (name == "Login")
                        return item.Login;
                    if (name == "Name")
                        return item.Name;
                    if (name == "orig__Id")
                        return p.GetIdentifier<UserAccountPartial, Guid>(item);
                    return Missing.Value;
                },
                (data, item, p) =>
                {
                    item.Login = data.GetString("Login");
                    item.Name = data.GetString("Name");
                });
        }

        static OverStoreConfiguration CreateUserAccountPartialConfig(string connectionString)
        {
            return OverStoreFluent.Configure()
                .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<UserAccountPartial, Guid>()
                    .WithDepot(Component.Depot.WithInternalKey<UserAccountPartial, Guid>("Id"))
                    .WithMapper(CreateUserAccountPartialMapper())
                    .CommandsFromTable(false, Component.CreateTable("UserAccount")
                                        .Key<Guid>("Id")
                                        .Column<string>("Login")
                                        .Column<string>("Name"))
                    .NoChangesTracking().NoErrorTransforming().ByIdentifierAttaching(i => i.Id)
                .EndObjectRepository()
                .SetRedirect<IUserAccount, Guid, UserAccountPartial, Guid>()
                // Here we say: when I call any method of IPersistenceSession with type arguments 
                // IUserAccount and GetGuid, repeat same call with type arguments UserAccountPartial and GetGuid.
                //.WithRepository(new RedirectRepositoryConfiguration<IUserAccount, Guid, UserAccountPartial, Guid>())
            .End();
        }

        /// <summary>
        /// Here we test repository for original UserAccountPartial.
        /// </summary>
        [TestMethod]
        public void GetObjectWithRedirectIntegralTestOriginRepository()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateUserAccountPartialConfig(connectionString).CreateSession())
            {
                // Get system account info.
                var system = session.GetByIdentifier<UserAccountPartial, Guid>(new Guid("c0a23bf2-8266-440c-95e5-7ce2830687e2"));
                var system1 = session.GetByIdentifier<UserAccountPartial, Guid>(new Guid("c0a23bf2-8266-440c-95e5-7ce2830687e2"));
                // Get my info.
                var sergey = session.GetByIdentifier<UserAccountPartial, Guid>(new Guid("af7989bb-1acb-4d00-a49a-03551c3857cc"));
                Assert.AreSame(system, system1);
                Assert.AreNotSame(system, sergey);

                Assert.AreEqual(new Guid("c0a23bf2-8266-440c-95e5-7ce2830687e2"), system.Id);
                Assert.AreEqual("system", system.Login);
                Assert.AreEqual("System Account", system.Name);


                Assert.AreEqual(new Guid("af7989bb-1acb-4d00-a49a-03551c3857cc"), sergey.Id);
                Assert.AreEqual("sergey", sergey.Login);
                Assert.AreEqual("Sergey Tokarev", sergey.Name);
            }
        }

        /// <summary>
        /// Get object by id using interface instead of implementation type.
        /// For check result get object with same identifier using implementation type argument.
        /// If result of both methods are same, it means redirection is successful.
        /// </summary>
        [TestMethod]
        public void GetObjectWithRedirect()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateUserAccountPartialConfig(connectionString).CreateSession())
            {
                // Get system account info.
                var system = session.GetByIdentifier<IUserAccount, Guid>(new Guid("c0a23bf2-8266-440c-95e5-7ce2830687e2"));
                var systemOrigin = session.GetByIdentifier<UserAccountPartial, Guid>(new Guid("c0a23bf2-8266-440c-95e5-7ce2830687e2"));

                Assert.AreSame(systemOrigin, system);
            }
        }

        #endregion

        #region Get Object using composite key

        /* ***************************************
         * Here we learn how to use composite keys. CompositeWithAutoId keys 
         * are keys consist of few parts. Each part of key may be not 
         * unique, but combination of these parts is unique over all objects of specified type.
         * 
         * There is no table with strong and real composite key in our test 
         * database. But we have table, which is good candidate to simulate 
         * composite key. This is ItemCost table. ItemCost store changes of
         * Costs (product prices and currency rates) during time. It has Id name
         * that is surrogate primary key, but following set of fields (Item, CostType, ValidFrom) 
         * is good candidate for become composite key. There is no guarantee that combination 
         * of values for this name will be always unique, but in our test data they are unique.
         * 
         * 
         * Here is content of part of ItemCost table. Full columns and its values 
         * you can find into ItemCost table.
         * 
         * Item                                 CostType    ValidFrom           ValidTo Cost
         * ----------------------------------------------------------------------------------
         * ed84a0c5-47c1-414c-b68b-69d338a3d830	NULL	    01.01.2001 0:00:00	NULL	17,00
         * 5136773a-eca9-4daf-a7a6-52ef443ef6d5	NULL	    01.01.2001 0:00:00	NULL	40,00
         * 56c218ea-f396-48a4-a092-3b354b97efc0	NULL	    01.01.2001 0:00:00	NULL	67,00
         * 9838eef2-6c71-4fdd-9ea9-eea02e3fd723	NULL	    01.01.2001 0:00:00	NULL	21,00
         * a0a9a28e-979f-483b-9467-b95cc41fe31c	NULL	    01.01.2001 0:00:00	NULL	22,00
         * 
         ****/

        /// <summary>
        /// Class for composite key demo.
        /// </summary>
        class ItemCost
        {
            public Guid Item
            {
                get;
                set;
            } // Duplicate part of key

            public Guid? CostType
            {
                get;
                set;
            } // Duplicate part of key

            public DateTime ValidFrom
            {
                get;
                set;
            } // Duplicate part of key

            public DateTime? ValidTo
            {
                get;
                set;
            }

            public decimal Cost
            {
                get;
                set;
            }
        }

        static IComponentConfiguration<IMapper<ItemCost>> CreateItemCostMapper()
        {
            return Component.Mapper.Delegate<ItemCost>(
                (item, name, p) =>
                {
                    if (name == "Item")
                        return item.Item;
                    if (name == "CostType")
                        return item.CostType;
                    if (name == "ValidFrom")
                        return item.ValidFrom;
                    if (name == "ValidTo")
                        return item.ValidTo;
                    return Missing.Value;
                },
                (data, item, p) =>
                {
                    item.Item = data.GetGuid("Item");
                    item.CostType = data.GetNullableGuid("CostType");
                    item.ValidFrom = data.GetDateTime("ValidFrom");
                    item.ValidTo = data.GetNullableDateTime("ValidTo");
                    item.Cost = data.GetDecimal("Cost");
                });
        }


        /// <summary>
        /// Any object that supports correct equality comparsion (in most cases it should be immutable)
        /// can be used as key. If object does not contain data that duplicates key parts, 
        /// special <see cref="OverStore.Configuration.MapperElement{TInstance}"/> should 
        /// be used for provide data required for load object by key.
        /// 
        /// OverStore provides two classes that can be used as composite keys with 2 or 3 parts.
        /// </summary>
        static OverStoreConfiguration CreateItemCostConfiguration(string connectionString)
        {
            var cs = (TableCommandSetConfiguration)Component.CommandSet.FromTable(
                                Component.CreateTable("ItemCost")
                                         .Key<Guid>("Item")
                                         .Key<Guid?>("CostType")
                                         .Key<DateTime>("ValidFrom")
                                         .Column<DateTime?>("ValidTo")
                                         .Column<decimal>("Cost"),
                                false);
            cs.CustomSelect = Component.AdoCommand.ReadCommand("select * from ItemCost where  Item = @Item AND (CostType = @CostType OR (CostType IS NULL AND @CostType IS NULL)) AND ValidFrom = @ValidFrom")
                                     .NullableInputParameter("CostType", DbType.Guid)
                                     .InputParameter("Item", DbType.Guid)
                                     .InputParameter("ValidFrom", DbType.DateTime);

            return OverStoreFluent.Configure()
                .LogToConsole().ConnectToDatabase<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ForciblyCloseReaders)
                .CustomClassRepository<ItemCost, Key3<Guid, Guid?, DateTime>>()
                    .WithDepot(Component.Depot.Delegate<ItemCost, Key3<Guid, Guid?, DateTime>>(() => new ItemCost(),
                                     (inst, id) =>
                                     {
                                         inst.Item = id.First;
                                         inst.CostType = id.Second;
                                         inst.ValidFrom = id.Third;
                                     },
                                     Component.IdentifierGenerator.Delegate<ItemCost, Key3<Guid, Guid?, DateTime>>(
                                        (i, last) => KeyHelper.MultipartKey<Guid, Guid?, DateTime>(i.Item.As("Item"), i.CostType.As("CostType"), i.ValidFrom.As("ValidFrom")))))
                    .WithMapper(CreateItemCostMapper())
                    .WithCommands(cs)
                    .ByIdentifierAttaching(instance => new Key3<Guid, Guid?, DateTime>(instance.Item.As("Item"), instance.CostType.As("CostType"), instance.ValidFrom.As("ValidFrom")))
                .EndObjectRepository()
            .End();
        }

        /// <summary>
        /// Display how to get object using multipart key.
        /// Not very compact syntax.
        /// </summary>
        [TestMethod]
        public void GetObjectByMultipartKey()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateItemCostConfiguration(connectionString).CreateSession())
            {
                var key = KeyHelper.MultipartKey(
                        new Guid("56c218ea-f396-48a4-a092-3b354b97efc0").As("Item"),
                        default(Guid?).As("CostType"),
                        new DateTime(2001, 1, 1, 0, 0, 0).As("ValidFrom"));

                var itemCost = session.GetByIdentifier<ItemCost, Key3<Guid, Guid?, DateTime>>(key);
                Assert.AreEqual(key.First, itemCost.Item);
                Assert.AreEqual(key.Second, itemCost.CostType);
                Assert.AreEqual(key.Third, itemCost.ValidFrom);
                Assert.IsNull(itemCost.ValidTo);
                Assert.AreEqual(67.0M, itemCost.Cost);

                var key2 = KeyHelper.MultipartKey(
                        new Guid("56c218ea-f396-48a4-a092-3b354b97efc0").As("Item"),
                        default(Guid?).As("CostType"),
                        new DateTime(2001, 1, 1, 0, 0, 0).As("ValidFrom"));

                var itemCost2 = session.GetByIdentifier<ItemCost, Key3<Guid, Guid?, DateTime>>(key2);
                Assert.AreSame(itemCost, itemCost2);
            }
        }

        /// <summary>
        /// As simple keys, multipart keys also immutable. It means even key 
        /// created using object data, further changing of data does not affect keys.
        /// </summary>
        [TestMethod]
        public void GetObjectMultipartKeyImmutability()
        {
            var connectionString = CreateMyMoneyDbCopyAndConnectionString();
            using (var session = CreateItemCostConfiguration(connectionString).CreateSession())
            {
                var key = KeyHelper.MultipartKey(
                        new Guid("56c218ea-f396-48a4-a092-3b354b97efc0").As("Item"),
                        default(Guid?).As("CostType"),
                        new DateTime(2001, 1, 1, 0, 0, 0).As("ValidFrom"));

                var itemCost = session.GetByIdentifier<ItemCost, Key3<Guid, Guid?, DateTime>>(key);
                Assert.AreEqual(key.First, itemCost.Item);
                Assert.AreEqual(key.Second, itemCost.CostType);
                Assert.AreEqual(key.Third, itemCost.ValidFrom);
                Assert.IsNull(itemCost.ValidTo);
                Assert.AreEqual(67.0M, itemCost.Cost);

                var key2 = KeyHelper.MultipartKey(
                        new Guid("33a6a15c-0bf9-4748-9db3-f3c5d89b20f3").As("Item"),
                        new Guid?(new Guid("fb843292-0e19-465b-ac99-5cf854b3c434")).As("CostType"),
                        new DateTime(2003, 1, 1, 0, 0, 0).As("ValidFrom"));

                try
                {
                    session.GetByIdentifier<ItemCost, Key3<Guid, Guid?, DateTime>>(key2);
                    Assert.Fail("Getting object by non-exising key does not throw any exception");
                }
                catch (PersistenceOperationFailedException)
                {
                    // Do nothing. This exception is normal behavior.
                }

                // Update object data regarding composite key.
                itemCost.Item = key2.First;
                itemCost.CostType = key2.Second;
                itemCost.ValidFrom = key2.Third;

                var itemCost2 = session.GetByIdentifier<ItemCost, Key3<Guid, Guid?, DateTime>>(key);
                // Check object available by old key.
                Assert.AreSame(itemCost, itemCost2);

                try
                {
                    session.GetByIdentifier<ItemCost, Key3<Guid, Guid?, DateTime>>(key2);
                    Assert.Fail("Object available by changed key but shouldn't");
                }
                catch (PersistenceOperationFailedException)
                {
                    // Do nothing. Normal behavior.
                }
            }
        }

        #endregion
    }
}
